﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using Colaboradores.Models;

namespace Colaboradores.Persistencia
{
    public class DepartamentoExistenteException : Exception {}
    public class DepartamentoNaoExistenteException : Exception {}

    public class Persistencia_Depto
    {
        public static void Inserir(Cls_Depto pDepto)
        {
            SqlConnection con = Conexao.getInstancia();

            SqlCommand cInsert = new SqlCommand("INSERT INTO TAB_DEPARTAMENTO (COD, DSC) VALUES (@COD, @DSC)", con);
            cInsert.Parameters.AddWithValue("COD", pDepto.Cod);
            cInsert.Parameters.AddWithValue("DSC", pDepto.Dsc);

            Cls_Depto oDepto = ConsultarPorCodigo(pDepto.Cod);

            if (oDepto != null)
            {
                pDepto.Dsc = oDepto.Dsc;
                throw new DepartamentoExistenteException();
            }

            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }

            try
            {
                cInsert.ExecuteNonQuery();
            }
            finally
            {
                //con.Close();
            }
        }

        public static void Alterar(Cls_Depto pDepto)
        {
            SqlConnection con = Conexao.getInstancia();

            SqlCommand cAlterar = new SqlCommand("UPDATE TAB_DEPARTAMENTO SET DSC = @DSC WHERE COD = @COD", con);
            cAlterar.Parameters.AddWithValue("COD", pDepto.Cod);
            cAlterar.Parameters.AddWithValue("DSC", pDepto.Dsc);

            Cls_Depto oDepto = ConsultarPorCodigo(pDepto.Cod);

            if (oDepto == null)
            {
                throw new DepartamentoNaoExistenteException();
            }

            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }

            try
            {
                cAlterar.ExecuteNonQuery();
            }
            finally
            {
                //con.Close();
            }
        }

        public static void Excluir(Cls_Depto pDepto)
        {
            SqlConnection con = Conexao.getInstancia();

            SqlCommand cExcluir = new SqlCommand("DELETE FROM TAB_DEPARTAMENTO WHERE COD = @COD", con);
            cExcluir.Parameters.AddWithValue("COD", pDepto.Cod);

            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }

            try
            {
                cExcluir.ExecuteNonQuery();
            }
            finally
            {
                //con.Close();
            }
        }

        public static IList<Cls_Depto> Listar()
        {
            SqlConnection con = Conexao.getInstancia();
            SqlCommand cConsulta = new SqlCommand("SELECT COD, DSC FROM TAB_DEPARTAMENTO", con);
            Cls_Depto depto;
            IList<Cls_Depto> lstDepto = new List<Cls_Depto>();

            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }

            try
            {
                SqlDataReader rConsulta = cConsulta.ExecuteReader();

                while (rConsulta.Read())
                {
                    depto = new Cls_Depto(rConsulta.GetInt32(0), rConsulta.GetString(1));

                    lstDepto.Add(depto);
                }
                rConsulta.Close();
            }
            finally
            {
                //con.Close();
            }
            return lstDepto;
        }

        public static Cls_Depto ConsultarPorCodigo(int pCod)
        {
            SqlConnection con = new SqlConnection(Conexao.ConnectionString);//Conexao.getInstancia();
            SqlCommand cConsulta = new SqlCommand("SELECT DSC FROM TAB_DEPARTAMENTO WHERE COD = @COD", con);
            cConsulta.Parameters.AddWithValue("COD", pCod);

            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }

            SqlDataReader rConsulta = cConsulta.ExecuteReader();
            try
            {
                if (rConsulta.Read())
                {
                    return new Cls_Depto(pCod, rConsulta.GetString(0));
                }
                else
                {
                    return null;
                }
            }
            catch
            {
                return null;
            }
            finally
            {
                rConsulta.Close();
                con.Close();
            }
        }

        public static IList<Cls_Depto> ConsultarPorDescricao(String pDsc)
        {
            SqlConnection con = Conexao.getInstancia();
            SqlCommand cConsulta = new SqlCommand("SELECT COD, DSC FROM TAB_DEPARTAMENTO WHERE DSC LIKE '%' + @DSC + '%'", con);
            cConsulta.Parameters.AddWithValue("DSC", pDsc);
            Cls_Depto depto;
            IList<Cls_Depto> lstDepto = new List<Cls_Depto>();

            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }

            try
            {
                SqlDataReader rConsulta = cConsulta.ExecuteReader();

                while (rConsulta.Read())
                {
                    depto = new Cls_Depto(rConsulta.GetInt32(0), rConsulta.GetString(1));

                    lstDepto.Add(depto);
                }
                rConsulta.Close();
            }
            finally
            {
                //con.Close();
            }
            return lstDepto;
        }
    }
}